package com.xuyang.utils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.TreeMap;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

public class MySqlManager {
	public  ArrayList<String> tableNames=new ArrayList<String>();
	private final String str="com.mysql.jdbc.Driver";
	private final String url="jdbc:mysql://localhost:3306/menudatabase";
	private final String user="selector";
	private final String password="123456";
	private Connection connection=null;
	//第一次初始化时获取与数据库的连接，并将获取所有菜品大项名
	private MySqlManager() {
		try {
			Class.forName(str);
			connection=DriverManager.getConnection(url, user, password);
			updataTableNames();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		updataTableNames();
	}
	//次方法用于更新数据库的表格名称变化
	public void updataTableNames(){
		try {
			ResultSet set=connection.createStatement().executeQuery("select * from 表格名称表");
			while (set.next()) {
				tableNames.add(set.getString("tableName"));
			}
			System.out.println(tableNames.toString());
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public String getData(){
		JSONObject object=new JSONObject();
		for (String table : tableNames) {
			ArrayList<FoodMenu> menus=new ArrayList<FoodMenu>(); 
			ResultSet set=null;
			try {
				set=connection.createStatement().executeQuery("select foodName,foodPrice,foodUnitPrice from "+table);
				while (set.next()) {
					FoodMenu foodMenu=new FoodMenu(set.getString("foodName"), set.getInt("foodPrice"), set.getString("foodUnitPrice"));
					menus.add(foodMenu);
				}
				object.put(table, menus);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		System.out.println(object.toString());
		return object.toString();
	}
	//获取数据库版本
	public int getDatabaseVersion(){
		ResultSet set=null;
		int i=0;
		try {
			set = connection.createStatement().executeQuery("select version from 数据库版本号");
			set.next();
			i=set.getInt("version");
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return i;
	}
	//设置数据库版本
	public boolean setDatabaseVersion(int version){
		try {
			connection.createStatement().execute("update version set version="+version);
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}
	private static MySqlManager manager=new MySqlManager();
	public static MySqlManager getInstance(){
		return manager;
	}
}
